/**
 * 
 */
package com.wutianyi.poi;

import java.awt.Color;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;

import javax.swing.text.StyledEditorKit.BoldAction;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

/**
 * @author hanjiewu
 * 
 *         2013-4-24-上午11:39:48
 */
public class HSSFMain
{
    /**
     * 创建新的workbook
     * 
     * @throws IOException
     */
    @Test
    public void testNewWorkbook() throws IOException
    {
        Workbook workbook = new HSSFWorkbook();
        FileOutputStream fileOutputStream = new FileOutputStream("hssf.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        Workbook wb = new XSSFWorkbook();
        fileOutputStream = new FileOutputStream("xssf.xlsx");
        wb.write(fileOutputStream);
        fileOutputStream.close();
    }

    @Test
    public void testNewSheet() throws IOException
    {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("new sheet");
        Sheet sheet2 = wb.createSheet("second sheet");
        FileOutputStream fileOut = new FileOutputStream("new_sheet.xls");
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

    @Test
    public void testCreatingCell() throws IOException
    {
        Workbook wb = new HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();

        Sheet sheet = wb.createSheet("new sheet");
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue(1.2);
        row.createCell(2).setCellValue(createHelper.createRichTextString("This is String"));
        row.createCell(3).setCellValue(true);
        FileOutputStream fileOut = new FileOutputStream("new_cell.xls");
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

    @Test
    public void testCreateDateCells() throws IOException
    {
        Workbook wb = new HSSFWorkbook();
        // Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(0);

        // Create a cell and put a date value in it. The first cell is not
        // styled
        // as a date.
        Cell cell = row.createCell(0);
        cell.setCellValue(new Date());

        // we style the second cell as a date (and time). It is important to
        // create a new cell style from the workbook otherwise you can end up
        // modifying the built in style and effecting not only this cell but
        // other cells.
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        cell = row.createCell(1);
        cell.setCellValue(new Date());
        cell.setCellStyle(cellStyle);

        // you can also set date as java.util.Calendar
        cell = row.createCell(2);
        cell.setCellValue(Calendar.getInstance());
        cell.setCellStyle(cellStyle);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    }

    @Test
    public void testWorkingDifferentTypes() throws IOException
    {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
        Row row = sheet.createRow((short) 2);
        row.createCell(0).setCellValue(1.1);
        row.createCell(1).setCellValue(new Date());
        row.createCell(2).setCellValue(Calendar.getInstance());
        row.createCell(3).setCellValue("a string");
        row.createCell(4).setCellValue(true);
        row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    }

    @Test
    public void testVariousAlignment() throws IOException
    {
        Workbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();

        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow((short) 2);
        row.setHeightInPoints(30);

        createCell(wb, row, (short) 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM);
        createCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
        createCell(wb, row, (short) 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER);
        createCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
        createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
        createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
        createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }

    /**
     * Creates a cell and aligns it a certain way.
     * 
     * @param wb
     *            the workbook
     * @param row
     *            the row to create the cell in
     * @param column
     *            the column number to create the cell in
     * @param halign
     *            the horizontal alignment for the cell.
     */
    private static void createCell(Workbook wb, Row row, short column, short halign, short valign)
    {
        Cell cell = row.createCell(column);
        cell.setCellValue("Align It");
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
    }

    @Test
    public void testBorders() throws IOException
    {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(1);

        // Create a cell and put a value in it.
        Cell cell = row.createCell(1);
        cell.setCellValue(4);

        // Style the cell with borders all around.
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    }

    @Test
    public void iterateExcel() throws InvalidFormatException, IOException
    {
        Workbook wb = WorkbookFactory.create(new File("excel/support.xls"));

        Sheet sheet = wb.getSheetAt(0);
        for (Row row : sheet)
        {
            for (Cell cell : row)
            {
                System.out.print(cell.getStringCellValue());
                System.out.print("\t");
            }
            System.out.println();
        }
    }

    @Test
    public void testIterateCellWithControl() throws InvalidFormatException, IOException
    {
        Workbook wb = WorkbookFactory.create(new File("excel/datas.xls"));
        Sheet sheet = wb.getSheetAt(4);
        int rowStart = Math.min(15, sheet.getFirstRowNum());
        int rowEnd = Math.max(1400, sheet.getLastRowNum());

        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++)
        {
            Row r = sheet.getRow(rowNum);

            int lastColumn = Math.max(r.getLastCellNum(), 20);

            for (int cn = 0; cn < lastColumn; cn++)
            {
                Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
                if (c == null)
                {
                    // The spreadsheet is empty in this cell
                }
                else
                {
                    // Do something useful with the cell's contents
                }
            }
        }
    }

    @Test
    public void testGetCellContent() throws InvalidFormatException, IOException
    {
        Workbook wb = WorkbookFactory.create(new File("excel/datas.xls"));
        Sheet sheet = wb.getSheetAt(4);
        for (Row row : sheet)
        {
            for (Cell cell : row)
            {
                CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                System.out.print(cellRef.formatAsString());
                System.out.print(" - ");

                switch (cell.getCellType())
                {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell))
                    {
                        System.out.print(cell.getDateCellValue());
                    }
                    else
                    {
                        System.out.print(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.print(cell.getCellFormula());
                    break;
                default:
                }
                System.out.print("\t");
            }
            System.out.println();
        }
    }

    @Test
    public void testCustomColorFont() throws IOException
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        HSSFFont font = wb.createFont();
        font.setColor(HSSFColor.BLUE.index);
        font.setFontName("微软雅黑");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short)24);
        cellStyle.setFont(font);
        
        Sheet sheet  = wb.createSheet("sheet 1");
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("帖子");
        
        FileOutputStream fileOut = new FileOutputStream(new File("custom_color_font.xls"));
        wb.write(fileOut);
        fileOut.close();
    }

    public static void main(String[] args)
    {
        Workbook workbook = new HSSFWorkbook();
    }
}
